package com.ry.core.util;

import java.io.*;
import java.lang.reflect.*;
import java.util.*;
import java.text.SimpleDateFormat;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.springframework.stereotype.Service;

/**
 * 导出excel
 * 
 * @author liqw
 * @Date 2011-08-31
 * @version 1.0
 * @param <T>
 */
@Service
public class ExportExcel {

	public static Logger logger = Logger.getLogger(ExportExcel.class);

	/**
	 * 无表头EXCEL
	 * 
	 * @param title
	 * @param dataset
	 * @param out
	 * @throws Exception
	 */
	public static<T> void exportExcel(String title, Collection<T> dataset,
			OutputStream out) throws Exception {
		exportExcel(title, null, null, dataset, out);
	}

	/**
	 * 无表头EXCEL
	 * 
	 * @param title
	 * @param dataset
	 * @param out
	 * @throws Exception
	 */
	public static<T> void exportExcel(String title, List<String[]> dataset,
			OutputStream out) throws Exception {
		exportExcel(title, null, dataset, null, out);
	}

	/**
	 * 有表头EXCEL
	 * 
	 * @param title
	 * @param headers
	 * @param dataset
	 * @param out
	 * @throws Exception
	 */
	public static<T> void exportExcel(String title, String[] headers,
			Collection<T> dataset, OutputStream out) throws Exception {
		exportExcel(title, headers, null, dataset, out);
	}

	/**
	 * 有表头EXCEL
	 * 
	 * @param title
	 * @param headers
	 * @param dataset
	 * @param out
	 * @throws Exception
	 */
	public static<T> void exportExcel(String title, String[] headers,
			List<String[]> dataset, OutputStream out) throws Exception {
		exportExcel(title, headers, dataset, null, out);
	}

	/**
	 * 导出excel
	 * 
	 * @param title
	 *            表格标题名
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合
	 * @param out
	 * 
	 * @param pattern
	 *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 * @throws IOException
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws InvocationTargetException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 */
	private static<T> void exportExcel(String title, String[] headers,
			List<String[]> objectdata, Collection<T> dataset, OutputStream out)
			throws IOException, SecurityException, NoSuchMethodException,
			IllegalArgumentException, IllegalAccessException,
			InvocationTargetException {

		logger.debug("开始导出excel");

		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);

		HSSFCellStyle titleStyle = setTileStyle(workbook);
		HSSFCellStyle bodyStyle = setBodyStyle(workbook);

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);

		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(20);

		if (headers != null && headers.length > 0) {
			for (int i = 0; i < headers.length; i++) {
				// 自适应宽度
				// sheet.autoSizeColumn(i);
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(titleStyle);
				HSSFRichTextString text = new HSSFRichTextString(headers[i]);
				cell.setCellValue(text);

			}
		}

		if (dataset != null) {
			setDataByClass(sheet, dataset, row, bodyStyle);
		} else {
			setDataByObject(sheet, objectdata, row, bodyStyle);
		}

		workbook.write(out);

		logger.debug("导出excel结束");
	}

	/**
	 * 数组方式设置表体数据
	 * @param sheet
	 * @param objectdata
	 * @param row
	 * @param bodyStyle
	 */
	private static void setDataByObject(HSSFSheet sheet, List<String[]> objectdata,
			HSSFRow row, HSSFCellStyle bodyStyle) {
		int index = 0;
		for (String[] s : objectdata) {
			index++;
			row = sheet.createRow(index);
			for (int i = 0; i < s.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(bodyStyle);
				cell.setCellValue(s[i]);
			}
		}
	}

	/**
	 * 反射方式设置表体数据
	 * 
	 * @param t
	 * @param row
	 * @param bodyStyle
	 * @param pattern
	 * @throws SecurityException
	 * @throws NoSuchMethodException
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 */
	private static<T> void setDataByClass(HSSFSheet sheet, Collection<T> dataset,
			HSSFRow row, HSSFCellStyle bodyStyle) throws SecurityException,
			NoSuchMethodException, IllegalArgumentException,
			IllegalAccessException, InvocationTargetException {
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();

			Field[] fields = t.getClass().getDeclaredFields();
			for (int i = 0; i < fields.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(bodyStyle);
				Field field = fields[i];
				String fieldName = field.getName();
				String getMethodName = "get"
						+ fieldName.substring(0, 1).toUpperCase()
						+ fieldName.substring(1);

				Class<?> tCls = t.getClass();
				Method getMethod = tCls
						.getMethod(getMethodName, new Class[] {});
				Object value = getMethod.invoke(t, new Object[] {});
				// 判断值的类型后进行强制类型转换
				String textValue = null;
				if (value instanceof Integer) {
					int intValue = (Integer) value;
					textValue = String.valueOf(intValue);
				} else if (value instanceof Float) {
					float fValue = (Float) value;
					textValue = String.valueOf(fValue);
				} else if (value instanceof Double) {
					double dValue = (Double) value;
					textValue = String.valueOf(dValue);
				} else if (value instanceof Long) {
					long longValue = (Long) value;
					textValue = String.valueOf(longValue);
				} else if (value instanceof Boolean) {
					boolean bValue = (Boolean) value;
					textValue = "是";
					if (!bValue) {
						textValue = "否";
					}
				} else if (value instanceof Date) {
					Date date = (Date) value;
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					textValue = sdf.format(date);
				} else if (value instanceof byte[]) {

				} else {
					textValue = value.toString();
				}
				cell.setCellValue(textValue);
			}
		}
	}

	/**
	 * 设置表头样式
	 * 
	 * @param workbook
	 * @return
	 */
	private static HSSFCellStyle setTileStyle(HSSFWorkbook workbook) {

		// 生成一个样式
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		// 设置表头样式
		titleStyle.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成字体
		HSSFFont titleFont = workbook.createFont();
		titleFont.setFontHeightInPoints((short) 14);
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		titleStyle.setFont(titleFont);

		// 声明一个画图的顶级管理器
		// HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// 定义注释的大小和位置,详见文档
		// HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
		// 0, 0, 0, (short) 4, 2, (short) 6, 5));
		// 设置注释内容
		// comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		// comment.setAuthor("");

		return titleStyle;
	}

	/**
	 * 设置表体样式
	 * 
	 * @param workbook
	 * @return
	 */
	private static HSSFCellStyle setBodyStyle(HSSFWorkbook workbook) {
		// 设置内容样式
		HSSFCellStyle bodyStyle = workbook.createCellStyle();

		bodyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		bodyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		bodyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成字体
		HSSFFont bodyFont = workbook.createFont();
		bodyFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		bodyStyle.setFont(bodyFont);
		return bodyStyle;
	}

	
}
